package com.geez.bbl.projector;


import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileFilter;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

public class ZefaniaXmlToCSJar {

    Connection con_derby = null;

    public static final String UTF8_BOM = "\uFEFF";

    ArrayList<File> filesList = new ArrayList<File>();

    static Document doc = null;

    public ZefaniaXmlToCSJar() {

    }


    public String[] Bible_Books = new String[]{
            "ዘፍጥረት",
            "ዘጸአት",
            "ዘሌዋውያን",
            "ዘህልቍ",
            "ዘዳግም",
            "ኢያሱ",
            "መሣፍንቲ",
            "ሩት",
            "1ይ ሳሙኤል",
            "2ይ ሳሙኤል",
            "1ይ ነገስታት",
            "2ይ ነገስታት",
            "1ይ ዜና መዋዕል",
            "2ይ ዜና መዋዕል",
            "ዕዝራ",
            "ነህምያ",
            "ኣስቴር",
            "ኢዮብ",
            "ዳዊት",
            "ምሳሌ",
            "መክብብ",
            "መኃልየ መኃልየ",
            "ኢሳይያስ",
            "ኤርምያስ",
            "ሰቆቃው ኤርምያስ",
            "ሕዝቅኤል",
            "ዳንኤል",
            "ሆሴዕ",
            "ኢዮኤል",
            "አሞጽ",
            "አብድዩ",
            "ዮናስ",
            "ሚክያስ",
            "ናሆም",
            "ዕንባቆም",
            "ሶፎንያስ",
            "ሐጌ",
            "ዘካርያስ",
            "ሚልክያ",
            "ማቴዎስ",
            "ማርቆስ",
            "ሉቃስ",
            "ወንጌል ዮሐንስ",
            "ግብረ ሐዋርያት",
            "ሮሜ",
            "1ይ ቆሮንቶስ",
            "2ይ ቆሮንቶስ",
            "ገላትያ",
            "ኤፌሶን",
            "ፊልጲ",
            "ቆላስይስ",
            "1ይ ተሰሎንቄ",
            "2ይ ተሰሎንቄ",
            "1ይ ጢሞቴዎስ",
            "2ይ ጢሞቴዎስ",
            "ቲቶ",
            "ፊልሞና",
            "ዕብራውያን",
            "ያዕቆብ",
            "1ይ ጴጥሮስ",
            "2ይ ጴጥሮስ",
            "1ይ ዮሐንስ",
            "2ይ ዮሐንስ",
            "3ይ ዮሐንስ",
            "ይሁዳ",
            "ራእይ ዮሐንስ"};


    public String[] Bible_Books_EN = new String[]{"Genesis", "Exodus",
            "Leviticus", "Numbers", "Deuteronomy", "Joshua", "Judges", "Ruth",
            "1 Samuel", "2 Samuel", "1 Kings", "2 Kings", "1 Chronicles",
            "2 Chronicles", "Ezra", "Nehemiah", "Esther", "Job", "Psalm",
            "Proverbs", "Ecclesiastes", "Song of Solomon", "Isaiah",
            "Jeremiah", "Lamentations", "Ezekiel", "Daniel", "Hosea", "Joel",
            "Amos", "Obadiah", "Jonah", "Micah", "Nahum", "Habakkuk",
            "Zephaniah", "Haggai", "Zechariah", "Malachi", "Matthew", "Mark",
            "Luke", "John", "Acts", "Romans", "1 Corinthians", "2 Corinthians",
            "Galatians", "Ephesians", "Philippians", "Colossians",
            "1 Thessalonians", "2 Thessalonians", "1 Timothy", "2 Timothy",
            "Titus", "Philemon", "Hebrews", "James", "1 Peter", "2 Peter",
            "1 John", "2 John", "3 John", "Jude", "Revelation"};

    public int[] Bible_ChapterCount = new int[]{50, 40, 27, 36, 34,
            24, 21, 4, 31, 24, 22, 25, 29, 36, 10, 13, 10, 42, 150, 31, 12, 8,
            66, 52, 5, 48, 12, 14, 3, 9, 1, 4, 7, 3, 3, 3, 2, 14, 4, 28, 16,
            24, 21, 28, 16, 16, 13, 6, 6, 4, 4, 5, 3, 6, 4, 3, 1, 13, 5, 5, 3,
            5, 1, 1, 1, 22};

    public void loadJdbcDrivers(String lang) {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            con_derby = DriverManager.getConnection("jdbc:derby:" + lang
                    + ";create=true");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Creates the schema.
     *
     * @param version the version
     * @throws Exception the exception
     */
    public void createSchema(String version) throws Exception {
        PreparedStatement ps_derby = null;
        System.out.println("Creating TABLE " + version + "_bible");
        ps_derby = con_derby.prepareStatement("CREATE TABLE " + version
                + "_bible (" + "  verse_id int NOT NULL,"
                + "  book varchar(3) NOT NULL," + "  chapter int NOT NULL,"
                + "  verse int NOT NULL," + "  data blob NOT NULL,"
                + "  PRIMARY KEY  (verse_id)" + ")");
        ps_derby.executeUpdate();
        // bible_books_<lang>
        System.out.println("Creating TABLE bible_books_" + version);
        ps_derby = con_derby.prepareStatement("CREATE TABLE bible_books_"
                + version + " (" + "  book_number int NOT NULL,"
                + "  english varchar(20) NOT NULL," + "  " + version
                + " blob NOT NULL," + "  chapter_count int NOT NULL,"
                + "  PRIMARY KEY  (book_number))");
        ps_derby.executeUpdate();
        ps_derby.close();
    }

    /**
     * Import_bible.
     *
     * @param version the version
     * @throws Exception the exception
     */
    public void import_bible(String version, String selectedVersion) throws Exception {
        PreparedStatement ps_derby = null;
        ps_derby = con_derby.prepareStatement("insert into " + version
                + "_bible" + "	(verse_id, book, chapter, verse, data)"
                + "	values	(?, ?, ?, ?, ?)");
        int verse_id = 1;
        int book = 1;
        String verse_data = "";
        for (int i = 0; i < Bible_Books.length; i++) { // for every book
            //String book_name = Bible_Books[i];
            int chapter_count = Bible_ChapterCount[i];
            byte[] str = null;
            for (int chapter = 1; chapter <= chapter_count; chapter++) // for
            {
                for (int verse = 1; verse <= 1000; verse++) {
                    try {
                        verse_data = getBibleText(book, chapter, verse);
                    } catch (Exception e) {
                        break;
                    }
                    ps_derby.setInt(1, verse_id);
                    if (book < 40)
                        ps_derby.setString(2, book + "O");
                    else
                        ps_derby.setString(2, book + "N");
                    ps_derby.setInt(3, chapter);
                    ps_derby.setInt(4, verse);
                    str = removeTags(verse_data, true);
                    ps_derby.setBinaryStream(5, new ByteArrayInputStream(
                            str), str
                            .length);

                    ps_derby.addBatch();
                    verse_id++;
                }
            }
            book++;
        }
        ps_derby.executeBatch();
        ps_derby.close();

        ps_derby = con_derby.prepareStatement("insert into bible_books_"
                + version + " 	(book_number, english, " + version
                + ", chapter_count" + "	)	values	(?, ?, ?, ?	)");
        for (int i = 0; i < Bible_Books.length; i++) {
            ps_derby.setInt(1, (i + 1));
            ps_derby.setString(2, Bible_Books[i]);
            ps_derby.setBytes(3, Bible_Books[i].getBytes());
            ps_derby.setInt(4, Bible_ChapterCount[i]);
            ps_derby.addBatch();
        }
        ps_derby.executeBatch();
        ps_derby.close();
    }


    private String getBibleText(int book, int chapter, int verse) {
        // book, verse, chapter starts with 1
        //System.out.println("Importing..."+Bible_Books[book-1]+" "+chapter+":"+verse);
        //
        Element elmt = (Element) doc.selectSingleNode("/XMLBIBLE/BIBLEBOOK[@bnumber='" + book + "']/CHAPTER[@cnumber='" + chapter + "']/VERS[@vnumber='" + verse + "']");

        if (elmt == null)
            throw new RuntimeException("Not found!");

        return elmt.getTextTrim();
    }

    public static void main(String[] args) throws Exception {
        String selectedVersion = null;
        File jfc = getZaphinaFile(args);
        if (jfc == null) return;


        String version = getBibleName(args);
        if (version == null)
            return;


        ZefaniaXmlToCSJar olang = new ZefaniaXmlToCSJar();

        doc = DocumentHelper.parseText(readUTFFile(jfc));

        System.out.println("Removing any old extractions...");
        olang.recursiveDelete(new File(version));
        System.out.println("Loading JDBC Drivers");
        olang.loadJdbcDrivers(version);
        System.out.println("Creating Schema: " + version);
        olang.createSchema(version);
        olang.import_bible(version, selectedVersion);
        System.out.println("Closing Connecitons");
        olang.closeConnections(version);

        String filename = null;
        filename = version + ".jar";
        System.out.println("Creating jar File...");
        olang.recursiveList(new File(version));
        cstools.imp.xml.CreateJarFile.createJarArchive(new File(filename), olang.filesList);
        System.out.println("Removing temp files...");
        olang.recursiveDelete(new File(version));
        System.out.println("CS Jar Bible successfully created using Zefania XML.\n" + filename);
        JOptionPane.showMessageDialog(null, "CS Jar Bible successfully created using Zefania XML.\n" + new File(filename).getAbsolutePath());
    }

    private static String getBibleName(String[] args) {
        if (args.length > 1) {
            return args[1];
        } else {
            return JOptionPane.showInputDialog("Enter the bible-code or language:\n(E.g, for King James Version, enter KJV)", "BIBLE");
        }
    }

    private static File getZaphinaFile(String[] args) {
        if (args.length > 0) {
            File file = new File(args[0]);
            if (file.exists()) {
                return file;
            } else {
                return null;
            }
        }

        JFileChooser jfc = new JFileChooser();
        jfc.setFileFilter(new FileFilter() {

            @Override
            public boolean accept(File f) {
                return f.getName().toLowerCase().endsWith(".xml");
            }

            @Override
            public String getDescription() {
                return "Zefania XML Bible";
            }
        });
        jfc.setDialogTitle("Open Zefania XML Bible xml file");
        int c = jfc.showOpenDialog(null);
        if (c != JFileChooser.APPROVE_OPTION) {
            return null;
        }
        return jfc.getSelectedFile();
    }

    public static String readUTFFile(File file) throws IOException {
        InputStream in = new FileInputStream(file);
        InputStreamReader isr = new InputStreamReader(in, "UTF8");
        BufferedReader br = new BufferedReader(isr);

        StringBuffer buf = new StringBuffer();
        String line = new String();
        while ((line = br.readLine()) != null) {
            buf.append(line);
            buf.append('\n');
        }
        isr.close();
        in.close();
        String xml = buf.toString();
        if (xml.startsWith(UTF8_BOM))
            xml = xml.substring(1);
        return xml;
    }

    public void recursiveDelete(File dir) {
        if (dir == null)
            return;
        if (dir.isFile()) {
            dir.delete();
            return;
        }
        File[] entries = dir.listFiles();
        if (entries == null) {
            return;
        }
        for (File entry : entries) {
            recursiveDelete(entry);
        }
        dir.delete();
    }

    /**
     * Recursive list.
     *
     * @param dir the dir
     */
    public void recursiveList(File dir) {
        if (dir == null)
            return;
        if (dir.isFile()) {
            filesList.add(dir);
            return;
        }
        filesList.add(dir);
        File[] entries = dir.listFiles();
        if (entries == null) {
            return;
        }
        for (File entry : entries) {
            recursiveList(entry);
        }
    }

    /**
     * Close connections.
     *
     * @param lang the lang
     * @throws Exception the exception
     */
    private void closeConnections(String lang) throws Exception {
        con_derby.commit();
        con_derby.close();
        try {
            DriverManager
                    .getConnection("jdbc:derby:" + lang + ";shutdown=true");
        } catch (Exception e) {
            System.out.println(e.getLocalizedMessage());
        }
    }

    /**
     * Removes the tags.
     *
     * @param verse   the verse
     * @param unicode the unicode
     * @return the byte[]
     */
    private byte[] removeTags(String verse, boolean unicode) {
        StringBuffer sb = new StringBuffer();
        boolean track = true;
        for (int i = 0; i < verse.length(); i++) {
            if (verse.charAt(i) == '<') {
                track = false;
                continue;
            }
            if (verse.charAt(i) == '>') {
                track = true;
                sb.append(" ");
                continue;
            }
            if (!track)
                continue;
            sb.append(verse.charAt(i));
        }
        try {
            if (unicode)
                return sb.toString().getBytes("UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return sb.toString().getBytes();
    }
}
